#!/usr/bin/env python
# encoding: utf-8
#################
# Name:        更新用户行为数据 
# Purpose:
#
# Author:      stephen
#
# Created:     02/20/2019
# Licence:     1.0.0
#
#################
import logging
import ConfigParser
import re, os, random, sys, string, hashlib, math, uuid
import calendar
import json,struct
import numpy as np
import pymysql

#from datetime import *
import datetime
import time
from LOCAL_CONFIG import *
from phone import Phone
from birth import Birth


reload(sys)
sys.setdefaultencoding('utf-8')
sys.path.append(os.environ['HOME']+"/lib")
from pyctlib import *

VERSION_NUM  = '1.0.0'
VERSION_DATE = '2019-02-20'

class UniDB:
  def __init__(self,user,passwd,server,port,dbname,dbtype="Oracle"):
    self.__server = server
    self.__port   = port
    self.__dbname = dbname
    self.__user   = user
    self.__passwd = passwd
    self.__dbtype = dbtype.lower()
    self.dbconn   = None
  def __del__(self):
    pass
  def open(self):
    if self.__dbtype == "oracle":
      os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import cx_Oracle
      dsn_tns = cx_Oracle.makedsn(self.__server, self.__port  , self.__dbname)
      self.dbconn=cx_Oracle.connect(self.__user, self.__passwd, dsn_tns)
    elif self.__dbtype == "mysql":
      #os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import pymysql
      self.dbconn=pymysql.connect(host  =self.__server, port=self.__port, user=self.__user,\
                                  passwd=self.__passwd, db  =self.__dbname, charset='utf8')
    else:
      raise TypeError("OnlyOracle.")
    logging.info("DB[%s] Open."%self.__dbtype)
  def close(self):
    try:
      if self.dbconn:
        self.dbconn.close()
      self.dbconn=None
      logging.info("DB[%s]Closed."%self.__dbtype)
    except Exception,e:
      logging.info("DB[%s]ClosedExcept[%s]."%(self.__dbtype,e))
      self.dbconn=None
      
class UniCfg:
  def __init__(self,cfgfile):
    self.cfgfile=cfgfile
    self.load_cfg()
  def load_cfg(self):
    cf = ConfigParser.ConfigParser()
    cf.read(self.cfgfile)
    
    self.dbhost = cf.get("db"   , "dbhost")
    self.dbport = cf.getint("db", "dbport")
    self.dbname = cf.get("db"   , "dbname")
    self.dbuser = cf.get("db"   , "dbuser")
    self.dbpass = cf.get("db"   , "dbpass")
    self.dbtype = cf.get("db"   , "dbtype")
    
    self.logfile = cf.get("log","logfile")
    self.loglevel= cf.get("log","loglevel").upper()

class Handle:
  def __init__(self,udb=None,cfg=None):
    self.dbconn  = udb.dbconn
    self.cfg  = cfg

    self.__table           = 'yd_user_act_channel'
    self.__add_user_id     = 1
    self.__update_user_id  = 1    
    #阅读2点赞3评论4收藏5取消收藏6分享
    self.__act_type  = 3

    sql = "SELECT * FROM yd_channel_base WHERE owner_type IN(3) AND `status`=1"
    self.__channels = self.__select_db(sql, True)   



  def handle_data(self):
    #sql = "select * from usr_user_datasource_rel where templet_id=1 and user_business_id=136 "
    sql = "select * from usr_user_datasource_rel where templet_id=1 "
    res = self.__select_db(sql, True)
    if res is None:
      return

    index = 0
    cursor = self.dbconn.cursor()
    for re in res:
      if not re.get('user_id') or not re.get('user_business_id'):
        continue
      re['user_id']          = re.get('user_id')
      re['user_business_id'] = re.get('user_business_id')  
      cnt_user =  self.__table_cnt_count( 'usr_user_basic_info', \
                                             "user_id=%s"%( re['user_id']) )
      if cnt_user == 0:
        continue
      sqls = self.__user_act_channel_sql(re)
      if type(sqls) == list and len(sqls)>0:
        for sql in sqls:
          if sql:
            logging.info(sql)
            cursor.execute(sql)
            if index%500 == 0:
              logging.info('index %d db commit'%(index))
              self.dbconn.commit() 
            index +=1

    logging.info('end index %d db commit'%(index))                           
    self.dbconn.commit()                                                     
    cursor.close() 
   
  #用户关注的频道数量
  def __user_act_channel_sql(self,  data_dict):
    if type(data_dict) != dict or len(data_dict)<1:
      return
    sqls = [] 
    for re in self.__channels:
      if not re.get('inner_id') or not re.get('channel_name'):
        continue
   
      wh = "user_id = %s and channel_id1 = %s "%( data_dict.get('user_business_id'),\
             re.get('inner_id'))
      cnt = self.__table_cnt_count('yd_news_comment', wh)
      if cnt>0:
        add_dict= {}
        add_dict['user_id']      = data_dict.get('user_id')
        add_dict['channel_id']   = re.get('inner_id')
        add_dict['channel_name'] = re.get('channel_name')
        add_dict['act_type']     = self.__act_type
        add_dict['times']        = cnt
        sql = self.__get_sql(add_dict)
        if sql:
          sqls.append(sql)
    return sqls 

    

  def __get_sql(self, add_dict):
    if type(add_dict) != dict or len(add_dict)<1:
      return
    if not  add_dict.get('user_id')       or not add_dict.get('channel_id') or \
       not  add_dict.get('channel_name')  or not add_dict.get('act_type')   or \
       not  add_dict.get('times'):
      return

    if len(add_dict)<1:
      return

    sql = "select * from %s  where  user_id=%s and channel_id=%s"%(self.__table,\
            add_dict.get('user_id'), add_dict.get('channel_id')   ) 
    ret = self.__select_db(sql, True)

    sql = None
    if ret:
      if len(ret) != 1:
        return
      if not ret[0].get('log_id'):
        return
      add_dict['log_id'] =  ret[0].get('log_id')
      sql = self.__generate_update_sql(add_dict )
    else:
      sql =  self.__generate_insert_sql(add_dict)

    if sql:
      return sql
    

  #generate insert sql
  def __generate_insert_sql(self, add_dict ):
    if type(add_dict) != dict or len(add_dict) <1:
      return

    insert_sql = "insert into %s ( "%(self.__table )
    values_sql = "values ("
    for key, val in add_dict.items():
      insert_sql += key + ','
      if type(val) == int:
        values_sql += '%d'%(val) + ','
      elif type(val) == float:
        values_sql += '%f'%(val) + ','
      else:
        values_sql += "'%s'"%(val) +','
    insert_sql += "add_user_id, add_time" +','
    values_sql += "%d, '%s' "%(1, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) +','
    if insert_sql[-1] == ',':
      insert_sql = insert_sql[:-1] + ') '
    if values_sql[-1]==',':
      values_sql = values_sql[:-1] + ') '

    sql = insert_sql + values_sql
    return sql

  #generate sql                                                              
  def __generate_update_sql(self, add_dict ):
    if type(add_dict) != dict or len(add_dict) <1:
      return

    if not add_dict.get('log_id'):
      return

    wh = "where log_id = %s"%(add_dict['log_id'])
    update_key = 'log_id'

    update_sql = "update %s  set "%( self.__table )                                          
    for key, val in add_dict.items():                                        
                                                                             
      if key == update_key:                                                  
        continue                                                             
      if type(val) == int:                                                   
        update_sql +=  '%s = %d'%(key, val) + ','                            
      elif type(val) == float:                                               
        update_sql += ' %s = %f'%(key, val) + ','                            
      else:                                                                  
        update_sql += "%s = '%s'"%(key, val) +','                            
    update_sql += "update_user_id = %d, update_time='%s' "%(1, \
                 datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) +',' 
    if update_sql[-1]==',':                                                  
      update_sql = update_sql[:-1]                                           
    update_sql += wh                                                         

    return update_sql  




	
  #通过select count 查询数据
  def __table_cnt_count(self,  table=None, wh=None ):
    if table == None:
      raise TypeError('tale name is errro')
    sql = "select count(1) from %s "%(table)
    if wh:
      sql +=  'where %s'%( wh)                                               
   
    #print(sql) 
    res = self.__select_db(sql)                                              
    #print( res )                                                            
    return res[0][0]  
  

  def __update_db(self, sql):
    try:
      cursor = self.dbconn.cursor()
      cursor.execute(sql)
      logging.info(sql)
      self.dbconn.commit()
      cursor.close()
    except Exception,err:
      logging.warning('err:%s' %(err) )

  def __select_db(self, sql, dictionary=None):
    try:
      if dictionary:
        cursor = self.dbconn.cursor( pymysql.cursors.DictCursor )
      else:
        cursor = self.dbconn.cursor()                                        

      self.dbconn.commit()
      cursor.execute(sql )                                                    
      res = cursor.fetchall()                                                
      cursor.close()                                                         
      return res                                                             
    except Exception,err:                                                    
      logging.warning('err:%s' %(err) )  

  def __get_before_ndays(self, days):
    now = datetime.datetime.now()
    delta = datetime.timedelta(days)
    n_days = now - delta
    return n_days


   

def main():
   if len(sys.argv)<>3 or sys.argv[1]<>"-f":
     print "Run Like: %s -f %s.conf"%(sys.argv[0][:-3],sys.argv[0][:-3])
     exit(1)
   cfg_file = None
   if sys.argv[2].find('/')>=0:
     cfg_file=sys.argv[2].find('/')
   else:
     cfg_file=os.environ['HOME']+"/cfg/"+sys.argv[2]
   cfg = UniCfg(cfg_file)
   
   if cfg.loglevel == "DEBUG":
     iLogLevel = logging.DEBUG
   elif cfg.loglevel == "INFO":
     iLogLevel = logging.INFO
   elif cfg.loglevel == "WARN" or cfg.loglevel == "WARNING":
     iLogLevel = logging.WARNING
   else:
     iLogLevel = logging.ERROR
   logging.basicConfig( filename=cfg.logfile,
                        format  = "%(asctime)s %(levelname)s[%(process)d]:%(message)s",
                        level   = iLogLevel)
   
   logging.info("......START.........")
   UDB = UniDB(cfg.dbuser,cfg.dbpass,cfg.dbhost,cfg.dbport,cfg.dbname,cfg.dbtype)
   UDB.open()
   obj = Handle(UDB,cfg)                                                     
   obj.handle_data() 
   
   UDB.close()
   logging.info("......EXIT.........")
   logging.shutdown()
   return

if __name__ == "__main__":
  main()
